package com.chj.mybatis.capt2;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.chj.mybatis.entity.EmailSexBean;
import com.chj.mybatis.entity.TUser;
import com.chj.mybatis.mapper.TUserMapper;
import com.chj.mybatis.mapper.TUserMapperExt;

public class MybatisDemo {
	
	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void init() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		// 1.读取mybatis配置文件创SqlSessionFactory
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		inputStream.close();
	}
	
	/**
	 *  test auto mapping
	 */
	@Test
	public void testAutoMapping() {
		// 2. achieve sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 3. achieve mapper
		TUserMapperExt extmapper = sqlSession.getMapper(TUserMapperExt.class);
		// 4. execute query and return result
		TUser user = extmapper.selectByPrimaryKeyAutoMapper(1);
		System.out.println(user.toString());
	}
	
	/**
	 * more param query
	 */
	@Test
	public void testMoreParamQuery() {
		// 2. achieve sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 3. achieve mapper
		TUserMapperExt mapperExt = sqlSession.getMapper(TUserMapperExt.class);
		
		String email = "qq.com";
		Byte sex = 1;
		
		// method 1: use map as param
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("email", email);
		params.put("sex", sex);
		List<TUser> list1 = mapperExt.selectByEmailAndSex1(params);
		System.out.println("list1==="+list1.size());
		
		// method 2: use param directly
		List<TUser> list2 = mapperExt.selectByEmailAndSex2(email, sex);
		System.out.println("list2==="+list2.size());
		
		// method 3: use map as param
		EmailSexBean esb = new EmailSexBean();
		esb.setEmail(email);
		esb.setSex(sex);
		List<TUser> list3 = mapperExt.selectByEmailAndSex3(esb);
		System.out.println("list3==="+list3.size());
		
	}
	
	/**
	 * 	测试插入数据自动生成id
	 * @throws IOException
	 */
	@Test
	public void testInsertGenerateId1() throws IOException {
		// 2.获取sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 3.获取对应mapper
		TUserMapperExt mapper = sqlSession.getMapper(TUserMapperExt.class);
		// 4.执行查询语句并返回结果
		TUser user1 = new TUser();
		user1.setUserName("test1");
		user1.setRealName("realname1");
		user1.setEmail("myemail1");
		mapper.insert1(user1);
		sqlSession.commit();
		System.out.println(user1.getId());
	}

	/**
	 * 	测试插入数据自动生成id
	 * @throws IOException
	 */
	@Test
	public void testInsertGenerateId2() throws IOException {
		// 2.获取sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 3.获取对应mapper
		TUserMapperExt mapper = sqlSession.getMapper(TUserMapperExt.class);
		// 4.执行查询语句并返回结果
		TUser user2 = new TUser();
		user2.setUserName("test2");
		user2.setRealName("realname2");
		user2.setEmail("myemai2l");
		mapper.insert2(user2);
		sqlSession.commit();
		System.out.println(user2.getId());
	}
	
	
	@Test
	// 注解测试
	public void testAnno() {
		// 2.获取sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 3.获取对应mapper
		TUserMapperExt mapperExt = sqlSession.getMapper(TUserMapperExt.class);
		
		List<TUser> list = mapperExt.selectByUserIdAnnotation(1);
		System.out.println(list.size());
		
		List<TUser> listAll = mapperExt.selectAllAnnotation();
		System.out.println(listAll.size());
		
		// 4.执行查询语句并返回结果
		TUser user = new TUser();
		user.setUserName("lisi");
		user.setRealName("ssdsfdf");
		user.setEmail("sdgsg");
		mapperExt.insertAnnotation(user);
		sqlSession.commit();
		System.out.println(user.getId());
	}
	
	/**
	 * 	 参数#和参数$区别测试(动态sql 入门)
	 * 	注意：两者区别在于传入的参数 是否加了 ‘’引号，#是有占位符 ？ 的 $是直接传入的参数
	 */
	@Test
	public void testSymbol() {
		// 2.获取sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 3.获取对应mapper
		TUserMapperExt mapperExt = sqlSession.getMapper(TUserMapperExt.class);
		
		String inCol = "id, user_name, real_name, sex, mobile, email, note";
		String tableName = "t_user";
		Byte sex = 1;
		String orderStr = "sex,user_name";
		
		List<TUser> list = mapperExt.selectBySymbol(tableName, inCol, orderStr, sex);
		System.out.println(list.size());
		
	}

	//--------------------------------动态sql---------------------------------------
	
	/**
	 * if用于select，并与where配合
	 */
	@Test
	public void testSelectIfOper() {
		// 2.获取sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 3.获取对应mapper
		TUserMapperExt mapper = sqlSession.getMapper(TUserMapperExt.class);
		
		String email = "qq.com";
		Byte sex = 1;
		// 如果设置为null sql会报错： where a.email like CONCAT('%', ?, '%') and 结尾and 没有删除
		
		// 不用<where> 查询
		List<TUser> list = mapper.selectIfOperate(email, sex);
		System.out.println(list.size());
		for(TUser user:list) {
			System.out.println(user.toString());
		}
		// 使用<where> 查询
		String email2 = "lison";
		Byte sex2 = null;
		List<TUser> list2 = mapper.selectIfandWhereOper(email2, sex2);
		System.out.println(list2.size());
		for(TUser user:list2) {
			System.out.println(user.toString());
		}
		
	}

	/**
	 * if用于update，并与set配合
	 * 	使用<set> 可以删除sql多余的 ‘，’
	 */
	@Test
	public void testUpdateIfOper() {
		// 2.获取sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 3.获取对应mapper
		TUserMapperExt mapper = sqlSession.getMapper(TUserMapperExt.class);
		
		TUser user = new TUser();
		user.setId(3);
		user.setUserName("cindy");
		user.setRealName("王美丽");
		user.setEmail("sss@163.com");
		user.setMobile("1235764532");
		user.setSex((byte) 2);
		
		//  update t_user set user_name = ?, real_name = ?, sex = ?, mobile = ?, email = ? where id = ?
//		System.out.println(mapper.updateIfAndSetOper(user));
		
		user.setNote("cindy's note");
		//  update t_user set user_name = ?, real_name = ?, sex = ?, mobile = ?, email = ?, where id = ?
		System.out.println(mapper.updateIfOperate(user));

		sqlSession.commit();
		
	}
	
	/**
	 * if用于insert，并与trim配合
	 */
	@Test
	public void testInsertIfOper() {
		// 2.获取sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 3.获取对应mapper
		TUserMapperExt mapper = sqlSession.getMapper(TUserMapperExt.class);
		
		TUser user = new TUser();
		user.setUserName("hankin");
		user.setRealName("chnehuajing");
		user.setEmail("1234@163.com");
		user.setMobile("18695988747");
		user.setSex((byte) 1);
//		user.setNote("hankin's note");
		// insert into t_user ( user_name, real_name, sex, mobile, email, ) values( ?, ?, ?, ?, ?, ) 
		System.out.println(mapper.insertIfOperate(user));
//		System.out.println(mapper.insertSelective(user));
		sqlSession.commit();
	}

	

}
